package com.apobates.jforum.grief.schema2doc.jdbc.dialect;

import com.apobates.jforum.grief.schema2doc.core.SchemaTableStrategy;
import com.apobates.jforum.grief.schema2doc.core.entity.Column;
import com.apobates.jforum.grief.schema2doc.core.entity.Nullable;
import com.apobates.jforum.grief.schema2doc.core.entity.PrimayKey;
import com.apobates.jforum.grief.schema2doc.core.entity.Table;
import com.apobates.jforum.grief.schema2doc.jdbc.schema.AbstractQueryExecutor;
import com.apobates.jforum.grief.schema2doc.core.schema.SchemaDialect;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
 * PostgreSQL的结果集供应商
 */
public class JooqQueryPostgreSQLExecutor extends AbstractQueryExecutor {
    private final static Logger logger = LoggerFactory.getLogger(JooqQueryPostgreSQLExecutor.class);

    public JooqQueryPostgreSQLExecutor(DataSource dataSource, SchemaDialect dialect) {
        super(dataSource, dialect);
    }

    @Override
    protected Stream<Table> getTables(DSLContext context, Optional<String> dbName, Optional<String> schema, SchemaTableStrategy tableStrategy) throws IllegalArgumentException {
        // String sql = "SELECT relname, obj_description(c.oid) AS description FROM pg_class c WHERE obj_description(c.oid) is not null";
        String sql = "SELECT t.tablename, obj_description(c.oid) AS description, c.oid FROM pg_tables t JOIN pg_class c ON t.tablename=c.relname WHERE t.schemaname=:schema";
        if(tableStrategy.sql(SchemaDialect.PostgreSQL).isPresent()){
            sql+= " AND " + tableStrategy.sql(SchemaDialect.PostgreSQL).get().replaceAll(SchemaTableStrategy.placeholder, "tablename");
        }
        logger.debug("[TABLE-PostgreSQL]execute sql statement::"+sql);
        return context.fetch(sql, DSL.param("schema", schema.get())).stream().map(record -> {
            // String dbName, String tableName, String remarks
            return new Table(
                    record.get("oid", int.class),
                    dbName.get(),
                    schema.get(),
                    record.get("tablename", String.class),
                    record.get("description", String.class));
        }).filter(table->tableStrategy.filter().test(table.getTableName()));
    }

    @Override
    protected Stream<Column> getColumns(DSLContext context, Table table) {
        final List<String> pks = getPrimayKey(context, table.getTableName());
        // https://www.postgresql.org/docs/current/infoschema-columns.html
        String sql = "SELECT " +
                "tmp.*, isc.numeric_scale, case when coalesce(character_maximum_length, numeric_precision, -1) = -1 then null else coalesce(character_maximum_length, numeric_precision, -1) end as column_size " +
                "FROM information_schema.columns isc " +
                "LEFT JOIN (" +
                "   SELECT a.attname as column_name,concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\\(.*\\)')) as udt_name, d.description,a.attnotnull as is_nullable,def.adsrc as column_default " +
                "   FROM pg_attribute a " +
                "   LEFT JOIN pg_description d ON d.objoid = a.attrelid and d.objsubid = a.attnum " +
                "   LEFT JOIN pg_class c ON a.attrelid = c.oid " +
                "   LEFT JOIN pg_type t ON a.atttypid = t.oid " +
                "   LEFT JOIN pg_attrdef def ON a.attrelid = def.adrelid AND a.attnum = def.adnum WHERE a.attnum >= 0 and c.relname = :tablename) tmp on tmp.column_name = isc.column_name " +
                "WHERE isc.table_name = :tablename";
        logger.debug("[COLUMN-PostgreSQL]execute sql statement::"+sql);
        // Fetch results using jOOQ
        return context.fetch(sql, DSL.param("tablename", table.getTableName()), DSL.param("tablename", table.getTableName())).stream().map(record -> {
            // Basic:: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
            String columnName = record.get("column_name", String.class);
            return Column.basic(
                            columnName,
                            record.get("udt_name", String.class),
                            record.get("column_size", String.class),
                            Nullable.getInstance(record.get("is_nullable", String.class)),
                            record.get("description", String.class))
                    .toFull(
                            record.get("numeric_scale", String.class),
                            pks.contains(columnName)?PrimayKey.YES:PrimayKey.NO,
                            record.get("column_default", String.class)); // String decimalDigits, PrimayKey pk, String columnDef
        });
    }

    private List<String> getPrimayKey(DSLContext context, String tableName){
        String sql = "SELECT result.COLUMN_NAME, result.KEY_SEQ, result.PK_NAME FROM(SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, (information_schema._pg_expandarray(i.indkey)).n AS KEY_SEQ, ci.relname AS PK_NAME, information_schema._pg_expandarray(i.indkey) AS KEYS, a.attnum AS A_ATTNUM FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN pg_catalog.pg_index i ON (a.attrelid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE i.indisprimary) result WHERE result.A_ATTNUM = (result.KEYS).x AND result.TABLE_NAME = :TN ";
        return context.fetch(sql, DSL.param("TN", tableName)).stream().map(record -> {
            return record.get("column_name", String.class);
        }).collect(Collectors.toList());
    }

}
